﻿using DAL.Entities;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace DAL.DisinfectDAL
{
    /// <summary>
    /// 消毒记录 Service
    /// </summary>
    public class DisinfectDAL
    {
        public readonly SqlDbHelper _dbHelper;
        public DisinfectDAL()
        {
            _dbHelper = new SqlDbHelper();
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns></returns>
        public bool Add(DisinfectModel model)
        {
            string sql = $@"INSERT INTO [dbo].[Disinfect]
           ([Id]
           ,[DeptId]
           ,[DeptName]
           ,[DisinfectUserId]
           ,[DisinfectUserName]
           ,[AreaId]
           ,[AreaName]
           ,[CreateUserId]
           ,[CreateUserName]
           ,[DisinfectTime]
           ,[Remark]
           ,[CreateTime])
     VALUES
           (@Id,
           @DeptId,
           @DeptName,
           @DisinfectUserId,
           @DisinfectUserName, 
           @AreaId,
           @AreaName, 
           @CreateUserId,
           @CreateUserName, 
           @DisinfectTime,
           @Remark, 
           @CreateTime)";
            SqlParameter[] paras = new SqlParameter[] {
                            new SqlParameter("@Id",model.Id),
                            new SqlParameter("@DeptId",model.DeptId ?? ""),
                            new SqlParameter("@DeptName",model.DeptName ?? ""),
                            new SqlParameter("@DisinfectUserId",model.DisinfectUserId ?? ""),
                            new SqlParameter("@DisinfectUserName",model.DisinfectUserName ?? ""),
                            new SqlParameter("@AreaId",model.AreaId ?? ""),
                            new SqlParameter("@AreaName",model.AreaName ?? ""),
                            new SqlParameter("@CreateUserId",model.CreateUserId ?? ""),
                            new SqlParameter("@CreateUserName",model.CreateUserName ?? ""),
                            new SqlParameter("@DisinfectTime",model.DisinfectTime),
                            new SqlParameter("@Remark",model.Remark ?? ""),
                            new SqlParameter("@CreateTime",model.CreateTime)
            };
            int success = _dbHelper.executeSqlNonQuery(sql, paras);
            return success > 0;
        }

        public bool Update(DisinfectModel model)
        {
            int success= _dbHelper.Update(model, "Disinfect", "Id",new List<string>() { "Files" });
            return success > 0;
        }

        public DisinfectModel GetDetail(string id)
        {
            string sql = "select * from Disinfect where id=@id";
            SqlParameter para = new SqlParameter("@id", id);
            DataTable dt =  _dbHelper.executeSqlDataTable(sql, para);
            return _dbHelper.DataTableToList<DisinfectModel>(dt).FirstOrDefault();
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id">主键ID</param>
        /// <returns></returns>
        public bool Del(string id)
        {
            string sql = " delete from Disinfect where id=@id; Delete from Base_File where recId=@id";
            SqlParameter paras = new SqlParameter("@id", id);
            int success = _dbHelper.executeSqlNonQuery(sql, paras);
            return success > 0;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页个数</param>
        /// <param name="deptId">组织机构</param>
        /// <param name="userId"></param>
        public IEnumerable<DisinfectModel> GetPagedList(int pageIndex, int pageSize, string deptId, string userId, string areaName, string startTime,string endTime,string userName, out int allCount)
        {

            List<SqlParameter> paras = new List<SqlParameter>();
            string where = " where 1=1 ";
            if (!string.IsNullOrWhiteSpace(deptId))
            {
                where += $" AND {nameof(DisinfectModel.DeptId)}=@deptId";
                paras.Add(new SqlParameter("@deptId", deptId));
            }
            if (!string.IsNullOrWhiteSpace(userId))
            {
                where += $" AND {nameof(DisinfectModel.DisinfectUserId)}=@userId";
                paras.Add(new SqlParameter("@userId", userId));
            }
            if (!string.IsNullOrWhiteSpace(areaName))
            {
                where += $" AND {nameof(DisinfectModel.AreaName)} like @areaName";
                paras.Add(new SqlParameter("@areaName", $"%{areaName}%"));
            }
            if (!string.IsNullOrWhiteSpace(userName))
            {
                where += $" AND {nameof(DisinfectModel.DisinfectUserName)} like @userName";
                paras.Add(new SqlParameter("@userName", $"%{userName}%"));
            }
            //开始时间
            DateTime start;
            if (!string.IsNullOrWhiteSpace(startTime) && DateTime.TryParse(startTime,out start))
            {
                where += $" AND {nameof(DisinfectModel.DisinfectTime)} >= @startTime";
                paras.Add(new SqlParameter("@startTime", start));
            }
            //结束时间
            DateTime end;
            if (!string.IsNullOrWhiteSpace(endTime) && DateTime.TryParse(endTime, out end))
            {
                where += $" AND {nameof(DisinfectModel.DisinfectTime)} < @endTime";
                paras.Add(new SqlParameter("@endTime", end.AddDays(1).Date));
            }
            string sql1 = $"select  count(*) from Disinfect  {where}";
            string sql2 = $"select * from(select  ROW_NUMBER() over(order by {nameof(DisinfectModel.DisinfectTime)} DESC) number,* FROM Disinfect  {where} )tt WHERE tt.number between {(pageIndex - 1) * pageSize + 1} and {pageSize * pageIndex}";
            object count = _dbHelper.GetScalar(sql1, paras.ToArray());
            allCount = Convert.ToInt32(count);
            DataTable dt = _dbHelper.executeSqlDataTable(sql2, paras.ToArray());
            var data = _dbHelper.DataTableToList<DisinfectModel>(dt);
            return data;

        }
    }
}
